package com.mjz.gui;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import javax.swing.border.TitledBorder;
import java.awt.*;
import java.sql.*;

import com.mjz.dao.Database;

public class CourseSelectFrame extends JInternalFrame {

    private final JTable courseTable;
    private final JComboBox<String> studentComboBox;
    private JComboBox<String> studentModifyComboBox;
    private JComboBox<String> courseModifyComboBox;

    public CourseSelectFrame(JDesktopPane desktopPane) {
        super("选课", true, true, true, true);
        setSize(600, 400);

        setLayout(new GridBagLayout());
        GridBagConstraints gbc = new GridBagConstraints();
        gbc.insets = new Insets(5, 5, 5, 5);
        gbc.anchor = GridBagConstraints.WEST;

        gbc.gridx = 0;
        gbc.gridy = 0;
        gbc.weightx = 0.2;
        add(new JLabel("学生姓名:"), gbc);

        gbc.gridx = 1;
        gbc.weightx = 0.6;
        studentComboBox = createComboBox("SELECT name FROM student");
        add(studentComboBox, gbc);

        gbc.gridx = 2;
        gbc.weightx = 0.2;
        add(new JLabel("班级:"), gbc);

        gbc.gridx = 3;
        gbc.weightx = 0.6;
        JComboBox<String> classComboBox = createComboBox("SELECT name FROM sclass");
        add(classComboBox, gbc);

        gbc.gridx = 4;
        gbc.weightx = 0.2;
        JButton selectCourseButton = new JButton("确认选课");
        add(selectCourseButton, gbc);
        selectCourseButton.addActionListener(e -> selectCourse());

        gbc.gridx = 0;
        gbc.gridy = 1;
        gbc.gridwidth = 5;
        gbc.fill = GridBagConstraints.BOTH;
        gbc.weightx = 1.0;
        gbc.weighty = 1.0;

        courseTable = new JTable();
        DefaultTableModel model = new DefaultTableModel(new Object[][]{}, new String[]{"选课编号", "学生姓名", "课程名称"});
        courseTable.setModel(model);
        courseTable.getTableHeader().setReorderingAllowed(false);

        JScrollPane scrollPane = new JScrollPane(courseTable);
        add(scrollPane, gbc);
        populateCourseTable();

        // Group Box
        JPanel modifyPanel = createModifyPanel();
        gbc.gridx = 0;
        gbc.gridy = 2;
        gbc.gridwidth = 5;
        gbc.fill = GridBagConstraints.HORIZONTAL;
        gbc.weighty = 0;
        add(modifyPanel, gbc);

        setLocation((desktopPane.getWidth() - getWidth()) / 2, (desktopPane.getHeight() - getHeight()) / 2);
    }

    private JPanel createModifyPanel() {
        JPanel panel = new JPanel(new GridBagLayout());
        panel.setBorder(new TitledBorder("修改选课信息"));
        GridBagConstraints gbc2 = new GridBagConstraints();
        gbc2.insets = new Insets(5, 5, 5, 5);
        gbc2.anchor = GridBagConstraints.WEST;

        gbc2.gridx = 0;
        gbc2.gridy = 0;
        gbc2.gridwidth = 2;
        panel.add(new JLabel("学生："), gbc2);

        gbc2.gridx = 2;
        gbc2.gridwidth = 2;
        studentModifyComboBox = createComboBox("SELECT name FROM student");
        panel.add(studentModifyComboBox, gbc2);

        gbc2.gridx = 4;
        gbc2.gridwidth = 1;
        panel.add(new JLabel("课程："), gbc2);

        gbc2.gridx = 5;
        gbc2.gridwidth = 1;
        courseModifyComboBox = new JComboBox<>();
        panel.add(courseModifyComboBox, gbc2);

        gbc2.gridy = 1;
        gbc2.gridx = 0;
        gbc2.gridwidth = 3;
        JButton confirmModifyButton = new JButton("确认修改");
        panel.add(confirmModifyButton, gbc2);

        gbc2.gridx = 4;
        gbc2.gridwidth = 7;
        JButton dropCourseButton = new JButton("退选课程");
        panel.add(dropCourseButton, gbc2);

        updateCourseModifyComboBox();
        confirmModifyButton.addActionListener(e -> modifyCourseSelection());
        dropCourseButton.addActionListener(e -> dropCourse());

        return panel;
    }

    private void updateCourseModifyComboBox() {
        courseModifyComboBox.removeAllItems();
        int studentId = getStudentId((String) studentComboBox.getSelectedItem());
        if (studentId == -1) return; //Handle the case where studentId is not found

        String sql = "SELECT c.name FROM selectedcourse sc JOIN course c ON sc.cid = c.id WHERE sc.sid = ?";
        try (Connection conn = Database.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, studentId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    courseModifyComboBox.addItem(rs.getString("name"));
                }
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "数据库错误: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        }
    }

    private void modifyCourseSelection() {
        int selectedRow = courseTable.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(this, "请选择要修改的课程!", "警告", JOptionPane.WARNING_MESSAGE);
            return;
        }
        int newCourseId = (int) courseTable.getValueAt(selectedRow, 0);
        int studentId = getStudentId((String) studentModifyComboBox.getSelectedItem());
        String oldCourseName = (String) courseModifyComboBox.getSelectedItem();

        if (oldCourseName == null || oldCourseName.isEmpty()) {
            JOptionPane.showMessageDialog(this, "请选择要修改的课程!", "警告", JOptionPane.WARNING_MESSAGE);
            return;
        }

        try (Connection conn = Database.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(
                     "UPDATE selectedcourse SET cid = ? WHERE sid = ? and cid = (select id from course where name = ?)")) {
            pstmt.setInt(1, newCourseId);
            pstmt.setInt(2, studentId);
            pstmt.setString(3, oldCourseName);
            int rowsAffected = pstmt.executeUpdate();
            if (rowsAffected > 0) {
                JOptionPane.showMessageDialog(this, "课程修改成功!", "成功", JOptionPane.INFORMATION_MESSAGE);
                populateCourseTable();
                updateCourseModifyComboBox();
            } else {
                JOptionPane.showMessageDialog(this, "课程修改失败!", "错误", JOptionPane.ERROR_MESSAGE);
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "数据库错误: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        }
    }

    private void dropCourse() {
        String courseName = (String) courseModifyComboBox.getSelectedItem();
        if (courseName == null || courseName.isEmpty()) {
            JOptionPane.showMessageDialog(this, "请选择要退选的课程!", "警告", JOptionPane.WARNING_MESSAGE);
            return;
        }
        int studentId = getStudentId((String) studentModifyComboBox.getSelectedItem());

        try (Connection conn = Database.getConnection();
             PreparedStatement preparedStatement = conn.prepareStatement(
                     "DELETE FROM selectedcourse WHERE sid = ? and cid = (select id from course where name = ?)")) {
            preparedStatement.setInt(1, studentId);
            preparedStatement.setString(2, courseName);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                JOptionPane.showMessageDialog(this, "退选成功!", "成功", JOptionPane.INFORMATION_MESSAGE);
                populateCourseTable();
                updateCourseModifyComboBox();
            } else {
                JOptionPane.showMessageDialog(this, "退选失败!", "错误", JOptionPane.ERROR_MESSAGE);
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "数据库错误: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        }
    }

    private JComboBox<String> createComboBox(String sql) {
        JComboBox<String> comboBox = new JComboBox<>();

        try (Connection conn = Database.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                comboBox.addItem(rs.getString("name"));
            }
        } catch (SQLException ignored) {
        }
        return comboBox;
    }

    private void populateCourseTable() {
        int studentId = getStudentId((String) studentComboBox.getSelectedItem());
        if (studentId == -1) {
            JOptionPane.showMessageDialog(this, "请选择学生!", "警告", JOptionPane.WARNING_MESSAGE);
            return;
        }

        DefaultTableModel model = new DefaultTableModel(new Object[][]{}, new String[]{"课程编号", "课程名称", "教师姓名", "最大人数", "已选人数"});
        courseTable.setModel(model);

        String sql = "SELECT c.id, c.name, t.name AS teacher_name, c.maxStuNum, c.selectedNum " +
                "FROM course c JOIN teacher t ON c.tid = t.id " +
                "WHERE c.id NOT IN (SELECT cid FROM selectedcourse WHERE sid = ?)";

        try (Connection conn = Database.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) { // 使用 PreparedStatement
            pstmt.setInt(1, studentId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    model.addRow(new Object[]{
                            rs.getInt("id"),
                            rs.getString("name"),
                            rs.getString("teacher_name"),
                            rs.getInt("maxStuNum"),
                            rs.getInt("selectedNum")
                    });
                }
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "数据库错误: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        }
    }

    private void selectCourse() {
        int selectedRow = courseTable.getSelectedRow();
        if (selectedRow == -1) {
            JOptionPane.showMessageDialog(this, "请选择要选的课程!", "警告", JOptionPane.WARNING_MESSAGE);
            return;
        }
        int courseId = (int) courseTable.getValueAt(selectedRow, 0);
        int studentId = getStudentId((String) studentComboBox.getSelectedItem());

        if (studentId == -1) {
            JOptionPane.showMessageDialog(this, "请选择学生!", "警告", JOptionPane.WARNING_MESSAGE);
            return;
        }

        String sql = "INSERT INTO selectedcourse (cid, sid) VALUES (?, ?)";
        try (Connection conn = Database.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) { // 使用 PreparedStatement
            pstmt.setInt(1, courseId);
            pstmt.setInt(2, studentId);
            int rowsAffected = pstmt.executeUpdate();
            if (rowsAffected > 0) {
                JOptionPane.showMessageDialog(this, "选课成功!", "成功", JOptionPane.INFORMATION_MESSAGE);
                populateCourseTable();
                updateCourseModifyComboBox(); // 更新下拉框
            } else {
                JOptionPane.showMessageDialog(this, "选课失败! 可能已选过此课或数据库错误", "错误", JOptionPane.ERROR_MESSAGE);
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "数据库错误: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
            ex.printStackTrace();
        }
    }

    private int getStudentId(String studentName) {
        try (Connection conn = Database.getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT id FROM student WHERE name = ?")) {
            stmt.setString(1, studentName);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt("id") : -1; // Return -1 if not found
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "数据库错误: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
            return -1;
        }
    }

    private int getCourseId(String courseName) {
        try (Connection conn = Database.getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT id FROM course WHERE name = ?")) {
            stmt.setString(1, courseName);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt("id") : -1; // Return -1 if not found
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(this, "数据库错误: " + ex.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
            return -1;
        }
    }
}